﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using EFB.Lib.Entity;

namespace EFB.Lib.Repository
{
   public class OrdProdRepository  : BaseRepository {
    
        public void Incluir(OrdProdEntity OperacaoProd)
        {

            Sql.ExecuteNonQuery(
            "INSERT INTO [efabric].[dbo].[EFB_ORDEMPROD] " +
            "       ( " +
            "       [SKU_ID] " +
            "       ,[DATA_PROGRAMA] " +
            "       ,[QTD_PROGRAMA] " +
            "       ,[QTD_ATENDIDA] " +
            "       ,[STATUS_OP] " +
            "       ,[SIMULACAO]) " +
            " VALUES " +
            "       ( " +
            "       @skuId, " +
            "       @dataProgramada, " +
            "       @qtdProgramada, " +
            "       0, " +
            "       'A', " +
            "       @simulacao) ", new SqlParameter[] { 
                
                new SqlParameter("@skuId", OperacaoProd.skuId),
                 new SqlParameter("@dataProgramada", OperacaoProd.dataPrograma),
                 new SqlParameter("@dataProgramada", OperacaoProd.dataPrograma), 
                new SqlParameter("@simulacao", OperacaoProd.simulacao)
            
            });


        }

        public void Incluir_Op(OrdProdEntity OperacaoProd)
        {

            Sql.ExecuteNonQuery(
            "INSERT INTO [EFB_ORDEMPROD] " +
            "       ( " +
            "       [SKU_ID] " +
            "       ,[DATA_PROGRAMA] " +
            "       ,[QTD_PROGRAMA] " +
            "       ,[QTD_ATENDIDA] " +
            "       ,[STATUS_OP] " +
            "       ,[SIMULACAO]) " +
            " VALUES " +
            "       ( " +
            "       @skuId, " +
            "       @dataProgramada, " +
            "       @qtdProgramada, " +
            "       0, " +
            "       'A', " +
            "       'N')  "
            , new SqlParameter[] {                 
                 new SqlParameter("@dataProgramada", OperacaoProd.dataPrograma),
                 new SqlParameter("@qtdProgramada", OperacaoProd.qtdPrograma), 
               // new SqlParameter("@simulacao", OperacaoProd.simulacao),                     
                new SqlParameter("@skuId", OperacaoProd.skuId) 
            
            });

            Object idOp = Sql.ExecuteScalar("SELECT @@IDENTITY ");
            OperacaoProd.opId = Convert.ToInt16(idOp);
            Sql.ExecuteNonQuery(
            " INSERT INTO [EFB_SKUFORMOP] " +
            "       ( " +
            "       [OP_ID] " +
            "       ,[SKUENTRADA_ID] " +
            "       ,[QUANTIDADE] " +
            "        ,[PCT_MINIMO]) " +
            "   SELECT @op,[SKUENTRADA_ID],([QUANTIDADE]*@qtdProgramada) ,[PCT_MINIMO]  " +
            "      FROM [EFB_SKUFORM] WHERE [SKUSAIDA_ID]=@skuId "
            , new SqlParameter[] {                 
                new SqlParameter("@op", Convert.ToInt16(idOp)),
                  new SqlParameter("@qtdProgramada", OperacaoProd.qtdPrograma),     
                new SqlParameter("@skuId", OperacaoProd.skuId) 
            
            });




        }


        public Boolean selectFomulacao(String skuId)
        {
            Boolean retorno = false;
            DataTable Table = Sql.ExecuteReader("DECLARE @op AS NUMERIC SET @op = 1      SELECT @op,[SKUENTRADA_ID],[QUANTIDADE] ,[PCT_MINIMO]  " +
            "      FROM [EFB_SKUFORM] WHERE [SKU_SAIDA_ID]=@skuId",
                new SqlParameter[] { new SqlParameter("@skuId", skuId) });
            if (Table != null && Table.Rows.Count > 0)
            {
                foreach (DataRow Row in Table.Rows)
                {
                    String val = Convert.ToString(Row["nome_coluna"]);
                }
            }
            return retorno;
        }

        public void CriaFormulacao_OP(OrdProdEntity OperacaoProd)
        {



        }

        public Boolean VerificaExitenciItemIdent_Rastrebilidade(String skuId)
        {
            Boolean retorno = false;
            DataTable Table = Sql.ExecuteReader("SELECT * FROM EFB_SKUFORM WHERE [SKU_SAIDA_ID]=@skuId",
                new SqlParameter[] { new SqlParameter("@skuId", skuId) });
            if (Table != null && Table.Rows.Count > 0)
            {
                foreach (DataRow Row in Table.Rows)
                {
                    String val = Convert.ToString(Row["nome_coluna"]);
                }
            }
            return retorno;
        }

        //operacoes de reserva da produção

        public int selecionarId_ITEMIDENTIF_Rastreabilidade(OrdProdEntity operacao)
        {

            int ret = 0;
            DataTable Table = Sql.ExecuteReader("SELECT A.[ITEMIDENTIF_ID] FROM [EFB_ITEMIDENTIF] AS A WHERE A.[RASTREABILIDADE] = @rast",
                new SqlParameter[] { new SqlParameter("@rast", null) });
            if (Table != null && Table.Rows.Count > 0)
            {
                foreach (DataRow Row in Table.Rows)
                {
                    ret = Convert.ToInt16(Row["ITEMIDENTIF_ID"]);
                }
            }
            return ret;


        }

        public void AtualizarQtdReserva_ITEMIDENTIF_Rastreabilidade(OrdProdEntity operacao, int itemIdentif)
        {

            Sql.ExecuteNonQuery(
            "UPDATE [EFB_ITEMIDENTIF] " +
            "SET QTD_RESERVADA = [QTD_RESERVADA] + @qtd_movimento  " +
            " WHERE [ITEMIDENTIF_ID] = @itemidentif_id "
       , new SqlParameter[] { 
                new SqlParameter("@qtd_movimento", operacao.qtdPrograma),
                new SqlParameter("@itemidentif_id", itemIdentif)              
            
            });


        }

        public int Insert_OPINSUM(OrdProdEntity OperacaoProd)
        {

            /* CALCULO PERCENTUAL MINIMO */

            Sql.ExecuteNonQuery(
            "INSERT INTO [EFB_OPINSUM] " +
            "       ( " +
            "       [SKUFORMOP_ID] " +
            "       ,[QUANTIDADE] " +
            "       ,[QUANT_MINIMA]) " +
            "      SELECT A.SKUFORMOP_ID, @qtd_movimento,  @qtd_movimento - (@qtd_movimento * 10 / 100)  " +
            "      FROM [EFB_SKUFORMOP] AS A  WHERE [OP_ID] = @op AND [SKUENTRADA_ID] = @sku"
            , new SqlParameter[] { 
                
                new SqlParameter("@qtd_movimento", OperacaoProd.qtdPrograma),
                new SqlParameter("@sku", OperacaoProd.skuId)       
            
            });

            return 0;
        }

        public int Insert_ReservaNaOP(OrdProdEntity OperacaoProd)
        {

            Sql.ExecuteNonQuery(
            "INSERT INTO [EFB_OPRESERV] " +
            "  ([ITEMIDENTIF_ID]  " +
            "  ,[OPINSUM_ID]  " +
            "  ,[DATA_RESERV]  " +
            "   ,[STATUS_RESERV]  " +
            "  ,[QTD_RESERVADA]  " +
            "   ,[QTD_ATENDIDA])  " +
            " SELECT @itemidentif_id, B.OPINSUM_ID, GETDATE(), 'A', @qtd_movimento, @qtd_movimento  " +
            "  FROM [EFB_SKUFORMOP] AS A  " +
            "  INNER JOIN [EFB_OPINSUM] AS B  " +
            "    ON B.[SKUFORMOP_ID] = A.[SKUFORMOP_ID]  " +
            "   WHERE A.[OP_ID] = @op AND A.[SKUENTRADA_ID] = @sku  "
            , new SqlParameter[] { 
                
                new SqlParameter("@qtd_movimento", OperacaoProd.qtdPrograma),
                new SqlParameter("@op", OperacaoProd.opId),
                new SqlParameter("@sku", OperacaoProd.skuId)       
            
            });

            return 0;
        }

        //operacoes de Registro de Producao


        public void Incluir_AvisoProducao(OrdProdEntity OperacaoProd)
        {

            Sql.ExecuteNonQuery(
               " INSERT INTO [efabric].[dbo].[EFB_MOVIMENTO] " +
          " ([MOVIMENTO_PAI_ID] " +
          " ,[ITEMIDENTIF_ID] " +
          " ,[SKU_ID] " +
          " ,[QUANTIDADE] " +
          " ,[OPERACAO_ID] " +
          " ,[STATUS_MOV] " +
          " ,[OP_ID] " +
          " ,[DATA_MOV]) " +
   "  VALUES " +
       "    (null " +
        "   ,@itemidentif_id " +
        "   ,@sku " +
        "   ,@qtd_movimento " +
        "   ,'Producao' " +
        "   ,0 " +
        "   ,null " +
        "   ,GETDATE())", new SqlParameter[] { 
                
                new SqlParameter("@sku", OperacaoProd.skuId),
                 new SqlParameter("@itemidentif_id", OperacaoProd.dataPrograma),
                 new SqlParameter("@qtd_movimento", OperacaoProd.qtdPrograma)
            
            });


        }

        public void AtualizarQtdReserva_ITEMIDENTIF(OrdProdEntity operacao, int itemIdentif)
        {
            Sql.ExecuteNonQuery(
            "UPDATE [efabric].[dbo].[EFB_ITEMIDENTIF] " +
            "SET QTD_RESERVADA = [QTD_RESERVADA] - @qtd_movimento,[QTD_BAIXADA] = [QTD_BAIXADA] + @qtd_movimento  " +
            " WHERE [ITEMIDENTIF_ID] = @itemidentif_id "
       , new SqlParameter[] { 
                new SqlParameter("@qtd_movimento", operacao.qtdPrograma),
                new SqlParameter("@itemidentif_id", itemIdentif)              
            
            });


        }

    }
}
